from PyQt5.QtSql import QSqlDatabase,QSqlQuery
from tablewidgets.MyTablePlayListFinal import *
def connectListInfo():
    database = QSqlDatabase()
    if QSqlDatabase.contains("qt_sql_default_connection"):
        database = QSqlDatabase.database("qt_sql_default_connection")
    else:
        database = QSqlDatabase.addDatabase("QSQLITE")
    database.open()
    database.exec("CREATE TABLE IF NOT EXISTS listinfo(id integer primary key,listname TEXT)")

def connectSongInfo():
    database = QSqlDatabase()
    if QSqlDatabase.contains("qt_sql_default_connection"):
        database = QSqlDatabase.database("qt_sql_default_connection")
    else:
        database = QSqlDatabase.addDatabase("QSQLITE")
    database.setDatabaseName("songdatabase.db")
    database.open()
    database.exec("create table if not exists songinfo(playlistname TEXT,id INT ,songname TEXT,songurl TEXT,duration TEXT,hash TEXT)")

def readListSongInfo(finalTable,listname):
    select_sql = "select * from songinfo where playlistname=? order BY id"

    sql_query = QSqlQuery()
    sql_query.prepare(select_sql)
    sql_query.addBindValue(listname)

    if sql_query.exec():
        while sql_query.next():
            strName = sql_query.value("songname")
            strDur = sql_query.value("duration")
            strUrl = sql_query.value("songurl")
            strHash = sql_query.value("hash")

            finalTable.addToPlayList(strName,strUrl,strDur,strHash,False)

def renameList(oldname,newname):
    query = QSqlQuery()
    query.exec("UPDATE listinfo SET listname='%(newname)' where listname = '%(oldname)'"%{"oldname":oldname,"newname":newname})
    query.exec("UPDATE songinfo SET playlistname = %(newname) WHERE  playlistname=%(oldname)"%{"oldname":oldname,"newname":newname})


def addSong(listname,songname,url,duration,strHash=""):
    query = QSqlQuery()
    query.prepare("select * from songinfo where playlistname=? and id=(select max(id) from songinfo where playlistname=?)")
    query.addBindValue(listname)
    query.addBindValue(listname)
    query.exec()

    index = 0
    while query.next():
        index = query.value("id")
        index+=1

    query.prepare("INSERT INTO songinfo(playlistname,id,songname,songurl,duration,hash) valus(:playlist,:id,:songname,:songurl,:duration,:hash)")
    query.bindValue(":playlistname",listname)
    query.bindValue(":id",index)
    query.bindValue(":songname",songname)
    query.bindValue(":songurl",url)
    query.bindValue(":duration",duration)
    query.bindValue(":hash",strHash)
    query.exec()

def addPlayList(plistname):
   query = QSqlQuery()
   query.prepare("INSERT INTO listinfo(listname) values(:listname)")
   query.bindValue(":listname",plistname)
   query.exec()

def deleteSong(listname,index):
    pass
def emptyList(listname):
    pass

def removePlayList(name):
    pass
def renameList(oldname,newname):
    pass
def swapList(url1,index1,url2,index2):
    pass
def takeAndInsert(f,t):
    pass
def readListSongInfo(FinalTable,listinfo):
    pass

def loadPlayList():
    pass